<?php  if ( ! defined('BASEPATH')) exit('No direct script access allowed');?>
<?php
    
    class Report_model extends CI_Model{
        public function __construct(){
            parent::__construct();
            
            $prefix = 'hrm_';
            $this->tblstaff = $prefix.'staffs';
            $this->tblpay = $prefix.'paygrades';
            $this->tbltimesheet = $prefix.'timesheets';
            $this->tbldepartment = $prefix.'departments';
        }
        
        public function getsearchname($key){
            $sql = "SELECT * FROM (`$this->tblstaff`) WHERE `active` = 1 AND (`codestaff` LIKE '%$key%' OR `firstname` LIKE '%$key%' OR `lastname` LIKE '%$key%') ORDER BY `firstname` desc";
            $data = $this->db->query($sql)->result_array();
            return $data;
        }
        
        public function getsearchpaygrade($key){
            $sql = "SELECT * FROM (`$this->tblpay`) WHERE `salary` LIKE '%$key%' OR `name` LIKE '%$key%' ORDER BY `salary` desc";
            $data = $this->db->query($sql)->result_array();
            return $data;
        }
        
        public function getpaygrade($key=2){
            $sql = "SELECT * FROM (`$this->tblpay`) WHERE `idpaygrade` = '$key'";
            $data = $this->db->query($sql)->row_array();
            return $data;
        }
        
        public function gettimesheetnstaff($param){
            $datestart  = $param['datestart'];
            $dateend    = $param['dateend'];
            $idstaff    = $param['idstaff'];

            $sql = "SELECT * FROM (`$this->tbltimesheet`) 
                    WHERE `datestart` >= '$datestart' AND `dateend` <= '$dateend' AND `idstaff` = '$idstaff' 
                    ";
                    
            $data = $this->db->query($sql)->result_array();
            
            return $data;
        }
        
        public function getsummary($param){
            $datestart  = $param['datestart'];
            $dateend    = $param['dateend'];

            $sql = "SELECT `staff`.`codestaff`, `staff`.`firstname`, `staff`.`lastname`, `staff`.`mobile`, `staff`.`active`, `dep`.`name`, sum(`time`.`key`) as totaltime
                    FROM (`$this->tbltimesheet` as time) 
                    INNER JOIN $this->tblstaff as staff ON staff.idstaff=time.idstaff
                    INNER JOIN $this->tbldepartment as dep ON dep.iddepartment=staff.iddepartment
                    WHERE `datestart` >= '$datestart' AND `dateend` <= '$dateend' 
                    GROUP BY `time`.`idstaff`
                    ";
                    
            $data = $this->db->query($sql)->result_array();
            
            return $data;
        }
    }

?>



